﻿using ExcelProcessingLibrary.DataStructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.InteropServices;
using InteropExcel = Microsoft.Office.Interop.Excel;

namespace ExcelProcessingLibrary
{
    public class ExcelGroupEngine
    {
        private InteropExcel.Worksheet _worksheet;
        private DataTable _rawData;
        private ReportConfiguration _report;
        private List<ColumnConfiguration> _columnList;
        private List<ColumnConfiguration> _visibleColumnList;
        private List<ColumnConfiguration> _ungroupedColumnList;
        private List<ColumnConfiguration> _visibleUngroupedColumnList;
        private List<ColumnConfiguration> _groupingColumnList;
        private List<GroupData> _groupDataList; //To hold the status of current level of grouping.
        private Dictionary<string, List<Decimal>> _totalDataList; //To hold the data for sub totalling and grand totalling.

        private int _currentRow;
        private int _grandTotalRowAtTop;

        /// <summary>
        /// Entry point to generate group report.
        /// </summary>
        /// <param name="rawData"></param>
        /// <param name="reportConfiguration"></param>
        public void GenerateGroupReport(InteropExcel.Worksheet worksheet, ReportConfiguration report)
        {
            _worksheet = worksheet;
            _rawData = report.DataSource;
            _report = report;
            _columnList = report.ColumnConfigurations.OrderBy(x => x.SheetColumn).ToList();

            _visibleColumnList = _columnList.Where(x => !x.Hidden).ToList();
            _ungroupedColumnList = _columnList.Where(x => !x.IsGroupingEnabled).ToList();
            _visibleUngroupedColumnList = _columnList.Where(x => !x.Hidden && !x.IsGroupingEnabled).ToList();
            _groupingColumnList = _columnList.Where(x => x.IsGroupingEnabled).OrderBy(x => x.GroupRank).ToList();
            _groupDataList = new List<GroupData>();
            _totalDataList = new Dictionary<string, List<decimal>>();

            MarkWeightedColumn();
            _currentRow = _report.TableStartRow;
            FormHeader();

            //If grand total for any of the column is configured to show in first row.
            if (_visibleUngroupedColumnList.Where(x => x.ShowGrandTotal && x.IsGrandTotalAtStartRow).Count() > 0)
                _grandTotalRowAtTop = _currentRow++;

            FormBody();
            FormatColumn();
            SetColumnWidth();
            CreateExcelTable();
        }

        /// <summary>
        /// Setting up the columns which is being used as weighted column.
        /// </summary>
        private void MarkWeightedColumn()
        {
            //For weighted average, subtotal & grandtotal data should be collected based on its base column.
            List<ColumnConfiguration> weightColumnEnabledList = _ungroupedColumnList.Where(x => !String.IsNullOrWhiteSpace(x.WeightColumn)).ToList();
            foreach (ColumnConfiguration weightColumnEnabled in weightColumnEnabledList)
            {
                ColumnConfiguration weightedColumn = _ungroupedColumnList.Where(x => x.SheetColumn == weightColumnEnabled.WeightColumn).FirstOrDefault();
                if (weightedColumn != null)
                {
                    if (weightColumnEnabled.ShowSubTotal) weightedColumn.IsWeightedSubTotal = true;
                    if (weightColumnEnabled.ShowGrandTotal) weightedColumn.IsWeightedGrandTotal = true;
                }
            }
        }

        /// <summary>
        /// To form the header of group report.
        /// </summary>
        private void FormHeader()
        {
            if (_visibleColumnList.Where(x => !x.HeaderHidden).Count() > 0)
            {
                foreach (ColumnConfiguration column in _visibleColumnList)
                {
                    if (!column.HeaderHidden)
                    {
                        _worksheet.get_Range(column.SheetColumn + _currentRow).Value = column.HeaderText;
                    }
                }
                _currentRow++;
            }
        }

        /// <summary>
        /// To form the data body of group report. 
        /// </summary>
        private void FormBody()
        {
            foreach (DataRow row in _rawData.Rows)
            {
                foreach (ColumnConfiguration groupingColumn in _groupingColumnList)
                {
                    GroupData(row, groupingColumn);
                }

                foreach (ColumnConfiguration dataColumn in _ungroupedColumnList)
                {
                    if (!dataColumn.Hidden) FormDataRow(row, dataColumn);

                    if (dataColumn.ShowSubTotal || dataColumn.IsWeightedSubTotal) CollectSubTotalData(row, dataColumn);
                    if (dataColumn.ShowGrandTotal || dataColumn.IsWeightedGrandTotal) CollectGrandTotalData(row, dataColumn);
                }
                _currentRow++;

                //If last row then create subtotal rows for remaining groups  & do grand totalling.
                if (row == _rawData.Rows[_rawData.Rows.Count - 1])
                {
                    FormSubTotalRows(_groupDataList);
                    FormGrandTotalRow();
                }
            }
        }

        /// <summary>
        /// To form the group row
        /// To create group level 
        /// To do sub totalling
        /// </summary>
        /// <param name="row"></param>
        /// <param name="groupingColumn"></param>
        private void GroupData(DataRow row, ColumnConfiguration groupingColumn)
        {
            string groupValue = row[groupingColumn.DataField].ToString();

            GroupData groupData = _groupDataList.Where(x => x.GroupColumn == groupingColumn).FirstOrDefault();
            if (groupData != null)
            {
                if (groupData.GroupValue == groupValue)
                {
                }
                else
                {
                    //If group value doesn't match, then break the grouping of all columns which has high rank value.
                    List<GroupData> groupDataHigherRankList = _groupDataList.Where(x => x.GroupColumn.GroupRank >= groupingColumn.GroupRank).ToList();
                    List<GroupData> groupDataLowerRankList = _groupDataList.Where(x => x.GroupColumn.GroupRank < groupingColumn.GroupRank).ToList();

                    foreach (GroupData groupDataHigherRank in groupDataHigherRankList)
                    {
                        _groupDataList.Remove(groupDataHigherRank);
                    }

                    FormSubTotalRows(groupDataHigherRankList, groupDataLowerRankList);
                    FormGroupRow(groupingColumn, groupValue);
                }
            }
            else
            {
                FormGroupRow(groupingColumn, groupValue);
            }
        }

        /// <summary>
        /// To create a group row & add group level to _groupDataList
        /// </summary>
        /// <param name="groupingColumn"></param>
        /// <param name="groupData"></param>
        private void FormGroupRow(ColumnConfiguration groupingColumn, string groupData)
        {
            _groupDataList.Add(new GroupData() { GroupColumn = groupingColumn, GroupValue = groupData, RowIndex = _currentRow }); //Add group level.
            if (!groupingColumn.HideGroupValue)
            {
                _worksheet.get_Range(groupingColumn.SheetColumn + _currentRow).Value = new String(' ', groupingColumn.Padding) + groupData;
            }
            _currentRow++;
        }

        /// <summary>
        /// To add the data row.
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dataColumn"></param>
        private void FormDataRow(DataRow row, ColumnConfiguration dataColumn)
        {
            if (dataColumn.ExcelDataType == ExcelDataType.Percentage)
            {
                decimal dataValue = 0;
                Decimal.TryParse(row[dataColumn.DataField].ToString(), out dataValue);
                _worksheet.get_Range(dataColumn.SheetColumn + _currentRow).Value = new String(' ', dataColumn.Padding) + (dataValue / 100);
            }
            else
                _worksheet.get_Range(dataColumn.SheetColumn + _currentRow).Value = new String(' ', dataColumn.Padding) + row[dataColumn.DataField];
        }

        /// <summary>
        /// To add the group level data for sub total.
        /// Key for referring data for sub total is (GroupColumn.UniqueKey + ..... + GroupColumn.UniqueKey + DataColumn.UniqueKey)   
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dataColumn"></param>
        private void CollectSubTotalData(DataRow row, ColumnConfiguration dataColumn)
        {
            decimal value;
            Decimal.TryParse(row[dataColumn.DataField].ToString(), out value);
            string subtotalKey = String.Empty; // Key for referring data for sub total is (GroupColumn.UniqueKey + ..... + GroupColumn.UniqueKey + DataColumn.UniqueKey)
            foreach (GroupData item in _groupDataList)
            {
                subtotalKey += item.GroupColumn.UniqueKey;
                if (item.GroupColumn.IsSubTotalEnabled)
                {
                    List<Decimal> subTotalValues;
                    if (_totalDataList.ContainsKey(subtotalKey + dataColumn.UniqueKey))
                    {
                        subTotalValues = _totalDataList[subtotalKey + dataColumn.UniqueKey];
                    }
                    else
                    {
                        subTotalValues = new List<decimal>();
                        _totalDataList.Add(subtotalKey + dataColumn.UniqueKey, subTotalValues);
                    }
                    subTotalValues.Add(value);
                }
            }
        }

        /// <summary>
        /// To add the data for grand total.
        /// Key for referring data for sub total is (DataColumn.UniqueKey)   
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dataColumn"></param>
        private void CollectGrandTotalData(DataRow row, ColumnConfiguration dataColumn)
        {
            decimal value;
            Decimal.TryParse(row[dataColumn.DataField].ToString(), out value);
            string grandTotalKey = dataColumn.UniqueKey; //Key for referring data for grand total is (DataColumn.UniqueKey) 
            List<Decimal> grandTotalValues;
            if (_totalDataList.ContainsKey(grandTotalKey))
            {
                grandTotalValues = _totalDataList[grandTotalKey];
            }
            else
            {
                grandTotalValues = new List<decimal>();
                _totalDataList.Add(grandTotalKey, grandTotalValues);
            }
            grandTotalValues.Add(value);
        }

        /// <summary>
        /// To create sub total row/rows when group breaks.
        /// </summary>
        /// <param name="groupDataHigherRankList"></param>
        /// <param name="groupDataLowerRankList"></param>
        private void FormSubTotalRows(List<GroupData> groupDataHigherRankList, List<GroupData> groupDataLowerRankList = null)
        {
            string subtotalKey = String.Empty;
            if (groupDataLowerRankList != null)
            {
                //Include group key which is not broken.
                foreach (GroupData item in groupDataLowerRankList)
                {
                    subtotalKey += item.GroupColumn.UniqueKey;
                }
            }

            int subTotalNotAtGroupRowCount = 0;
            //If there is a column where sub total is enabled and printing is not at the same group level row.
            if (_visibleUngroupedColumnList.Where(x => x.ShowSubTotal && !x.IsSubTotalAtGroupRow).Count() > 0)
            {
                subTotalNotAtGroupRowCount = groupDataHigherRankList.Where(x => x.GroupColumn.IsSubTotalEnabled).Count();
            }
            //Sub total should be printed in reverse order, starting from high rank value to the low rank value 
            if (subTotalNotAtGroupRowCount > 0) _currentRow += (subTotalNotAtGroupRowCount - 1);
            foreach (GroupData groupDataHigherRank in groupDataHigherRankList)
            {
                subtotalKey += groupDataHigherRank.GroupColumn.UniqueKey;
                if (groupDataHigherRank.GroupColumn.IsSubTotalEnabled)
                {
                    foreach (ColumnConfiguration visibleUngroupedColumn in _visibleUngroupedColumnList)
                    {
                        if (visibleUngroupedColumn.ShowSubTotal)
                        {
                            // Key for referring data for sub total is (GroupColumn.UniqueKey + ..... + GroupColumn.UniqueKey + DataColumn.UniqueKey)
                            string key = subtotalKey + visibleUngroupedColumn.UniqueKey;
                            Decimal subTotalValue = 0;
                            if (visibleUngroupedColumn.TotalType == TotalType.WeightedAverage)
                            {
                                ColumnConfiguration weightColumn = _ungroupedColumnList.Where(x => x.SheetColumn == visibleUngroupedColumn.WeightColumn).FirstOrDefault();
                                if (weightColumn != null)
                                {
                                    subTotalValue = TotalData(visibleUngroupedColumn.TotalType, _totalDataList[key], _totalDataList[subtotalKey + weightColumn.UniqueKey]);
                                }
                            }
                            else
                            {
                                subTotalValue = TotalData(visibleUngroupedColumn.TotalType, _totalDataList[key]);
                            }

                            if (visibleUngroupedColumn.ExcelDataType == ExcelDataType.Percentage)
                                subTotalValue = subTotalValue / 100;

                            int subTotalRowIndex = 0;
                            if (visibleUngroupedColumn.IsSubTotalAtGroupRow)
                                subTotalRowIndex = groupDataHigherRank.RowIndex;
                            else
                            {
                                subTotalRowIndex = _currentRow;
                                _worksheet.get_Range(groupDataHigherRank.GroupColumn.SheetColumn + subTotalRowIndex).Value = groupDataHigherRank.GroupValue + " - TOTAL";
                            }

                            _worksheet.get_Range(visibleUngroupedColumn.SheetColumn + subTotalRowIndex).Value = subTotalValue;
                        }
                    }
                    if (subTotalNotAtGroupRowCount > 0) _currentRow--;
                }

                //To delete sub total data for current group level.
                string keyToDelete = String.Empty;
                foreach (ColumnConfiguration ungroupedColumn in _ungroupedColumnList)
                {
                    keyToDelete = (subtotalKey + ungroupedColumn.UniqueKey);
                    if (_totalDataList.ContainsKey(keyToDelete))
                    {
                        _totalDataList.Remove(keyToDelete);
                    }
                }
            }
            if (subTotalNotAtGroupRowCount > 0) _currentRow += (subTotalNotAtGroupRowCount + 1);
        }

        /// <summary>
        /// To create grand total row.
        /// </summary>
        private void FormGrandTotalRow()
        {
            foreach (ColumnConfiguration visibleUngroupedColumn in _visibleUngroupedColumnList)
            {
                if (visibleUngroupedColumn.ShowGrandTotal)
                {
                    //Key for referring data for grand total is (DataColumn.UniqueKey) 
                    string key = visibleUngroupedColumn.UniqueKey;
                    Decimal grandTotalValue = 0;
                    if (visibleUngroupedColumn.TotalType == TotalType.WeightedAverage)
                    {
                        ColumnConfiguration weightColumn = _ungroupedColumnList.Where(x => x.SheetColumn == visibleUngroupedColumn.WeightColumn).FirstOrDefault();
                        if (weightColumn != null)
                        {
                            grandTotalValue = TotalData(visibleUngroupedColumn.TotalType, _totalDataList[key], _totalDataList[weightColumn.UniqueKey]);
                        }
                    }
                    else
                    {
                        grandTotalValue = TotalData(visibleUngroupedColumn.TotalType, _totalDataList[key]);
                    }

                    if (visibleUngroupedColumn.ExcelDataType == ExcelDataType.Percentage)
                        grandTotalValue = grandTotalValue / 100;

                    int grandTotalRowIndex = visibleUngroupedColumn.IsGrandTotalAtStartRow ? _grandTotalRowAtTop : _currentRow;

                    _worksheet.get_Range(_visibleColumnList.First().SheetColumn + grandTotalRowIndex).Value = "GRAND TOTAL";
                    _worksheet.get_Range(visibleUngroupedColumn.SheetColumn + grandTotalRowIndex).Value = grandTotalValue;
                }
            }

            //To keep in sync with the other method where new row is created and then at the end counter should be incremented by +1.  
            if (_visibleUngroupedColumnList.Where(x => x.ShowGrandTotal && !x.IsGrandTotalAtStartRow).Count() > 0)
            {
                _currentRow++;
            }

            //To delete grand total data for current group level.
            foreach (ColumnConfiguration ungroupedColumn in _ungroupedColumnList)
            {
                if (_totalDataList.ContainsKey(ungroupedColumn.UniqueKey))
                {
                    _totalDataList.Remove(ungroupedColumn.UniqueKey);
                }
            }
        }

        /// <summary>
        /// To do the grand totalling or sub totalling based on the total type.
        /// </summary>
        /// <param name="totalType"></param>
        /// <param name="valueList"></param>
        /// <returns></returns>
        private decimal TotalData(string totalType, List<Decimal> valueList, List<decimal> weightColumnValueList = null)
        {
            Decimal totalValue = 0;
            if (totalType == TotalType.Count)
                totalValue = valueList.Count;
            else if (totalType == TotalType.Sum)
                totalValue = valueList.Sum();
            else if (totalType == TotalType.Average)
                totalValue = valueList.Average();
            else if (totalType == TotalType.WeightedAverage)
            {
                if (valueList != null && weightColumnValueList != null && valueList.Count > 0 && weightColumnValueList.Count > 0 && valueList.Count == weightColumnValueList.Count)
                {
                    List<decimal> weightedValueList = new List<decimal>();
                    for (int i = 0; i < valueList.Count; i++)
                    {
                        weightedValueList.Add(valueList[i] * weightColumnValueList[i]);
                    }
                    decimal sumOfweightColumnValue = weightColumnValueList.Sum();
                    if (sumOfweightColumnValue > 0)
                    {
                        totalValue = weightedValueList.Sum() / sumOfweightColumnValue;
                    }
                }
            }

            return totalValue;
        }

        /// <summary>
        /// Apply data type on the used range for a column. 
        /// </summary>
        private void FormatColumn()
        {
            InteropExcel.Range columnRange = null;
            foreach (ColumnConfiguration visibleColumn in _visibleColumnList)
            {
                int startRow = _report.TableStartRow + (_visibleColumnList.Where(x => !x.HeaderHidden).Count() > 0 ? 1 : 0);
                columnRange = _worksheet.get_Range(visibleColumn.SheetColumn + startRow, visibleColumn.SheetColumn + (_currentRow - 1));

                string numberFormat = String.Empty;
                if (visibleColumn.ExcelDataType == ExcelDataType.String)
                    numberFormat = "@";
                else if (visibleColumn.ExcelDataType == ExcelDataType.Long)
                    numberFormat = "#,##0";
                else if (visibleColumn.ExcelDataType == ExcelDataType.Double)
                    numberFormat = "#,##0.00";
                else if (visibleColumn.ExcelDataType == ExcelDataType.Currency)
                    numberFormat = "$#,##0.00";
                else if (visibleColumn.ExcelDataType == ExcelDataType.Percentage)
                    numberFormat = "0.00%";
                else if (visibleColumn.ExcelDataType == ExcelDataType.Date)
                    numberFormat = "MM/DD/YYYY HH:MM AM/PM";
                else
                    numberFormat = "@";

                columnRange.NumberFormat = numberFormat;
            }

            ReleaseExcelObject(columnRange);
        }

        /// <summary>
        /// To set the column widhth.
        /// </summary>
        private void SetColumnWidth()
        {
            InteropExcel.Range columnRange = null;
            foreach (ColumnConfiguration visibleColumn in _visibleColumnList)
            {
                columnRange = _worksheet.get_Range(visibleColumn.SheetColumn + ":" + visibleColumn.SheetColumn);
                if (visibleColumn.ColumnWidth > 0)
                    columnRange.EntireColumn.ColumnWidth = visibleColumn.ColumnWidth;
                else
                    columnRange.EntireColumn.AutoFit();
            }

            ReleaseExcelObject(columnRange);
        }

        /// <summary>
        /// To create excel table from the used range.
        /// </summary>
        private void CreateExcelTable()
        {
            if (_report.CreateExcelTable && _report.ExcelTableConfiguration != null)
            {
                int headerRow = _report.TableStartRow;
                //If header is hidden and CreateExcelTable is set to true, then create Excel table by considering (SheetStartRow - 1) as header row.  
                if (_visibleColumnList.Where(x => !x.HeaderHidden).Count() == 0) headerRow = _report.TableStartRow - 1;

                //Add blank space if Excel Table Header has blank text, else it will show like Column1, Column2, etc.
                InteropExcel.Range headerCell = null;
                int blankHeaderCounter = 0;
                foreach (ColumnConfiguration column in _visibleColumnList)
                {
                    headerCell = _worksheet.get_Range(column.SheetColumn + headerRow);
                    if (String.IsNullOrWhiteSpace(headerCell.Value))
                    {
                        blankHeaderCounter++;
                        headerCell.Value = new String(' ', blankHeaderCounter);
                    }
                }
                ReleaseExcelObject(headerCell);

                InteropExcel.Range reportRange = _worksheet.get_Range(_visibleColumnList.First().SheetColumn + headerRow, _visibleColumnList.Last().SheetColumn + (_currentRow - 1));
                InteropExcel.ListObject listObject = _worksheet.ListObjects.AddEx(InteropExcel.XlListObjectSourceType.xlSrcRange, reportRange, Type.Missing, InteropExcel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing);
                listObject.Name = _report.ExcelTableConfiguration.Name;
                listObject.TableStyle = _report.ExcelTableConfiguration.TableStyle;
                listObject.ShowAutoFilter = _report.ExcelTableConfiguration.ShowAutoFilter;

                ReleaseExcelObject(reportRange);
                ReleaseExcelObject(listObject);
            }
        }

        /// <summary>
        /// To release excel COM object.
        /// </summary>
        /// <param name="excelObject"></param>
        private void ReleaseExcelObject(object excelObject)
        {
            Marshal.FinalReleaseComObject(excelObject);
            excelObject = null;
        }
    }
}
